﻿using System;
using System.Data;
using System.Data.SqlServerCe;
using System.Collections.Generic;
using SerbinAV.MobileSales.Sales.Entities;


namespace SerbinAV.MobileSales.Sales.DAL
{
    public class OrderRepository
    {
        private string _connectionString;
        
        public OrderRepository (string connectionString)
        {
            _connectionString = connectionString;
        }

        private List<Order> loadOrders(SqlCeCommand command)
        {
            using (SqlCeDataReader dr = command.ExecuteReader())
            {
                List<Order> orders = new List<Order>(5);
                
                while (dr.Read())
                {
                    Order order = EntityReader.ReadOrder(dr);
                    //LoadOrderRecord
                    orders.Add(order);
                }
                return orders;
            }
        }

        public List<Order> GetOrdersByVisit(int idVisit)
        {
            using (SqlCeConnection connection = new SqlCeConnection(_connectionString))
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();

                string sqlQuery = "SELECT IdOrder, DateOrder, SumOrder, NumberOrder, VisitId, SkladId, State" +
                                  " FROM Orders" +
                                  " Where VisitId = @VisitId";
                using (SqlCeCommand com = new SqlCeCommand(sqlQuery, connection))
                {
                    com.Parameters.Add("@VisitId", idVisit);
                    return loadOrders(com);
                }
            }
        }

        //public List<Order> GetExportOrderByVisit(int idVisit)
        //{
        //    using (SqlCeConnection connection = WorkDataBase.CreateConnection())
        //    {
        //        if (connection.State != ConnectionState.Open)
        //            connection.Open();

        //        string sqlQuery = "SELECT IdOrder, DateOrder, SumOrder, NumberOrder, VisitId, SkladId, State" +
        //                          " FROM Orders" +
        //                          " Where VisitId = @VisitId and State not in ('V') ";
        //        using (SqlCeCommand com = new SqlCeCommand(sqlQuery, connection))
        //        {
        //            com.Parameters.Add("@VisitId", idVisit);
        //            return loadOrders(com);
        //        }
        //    }

        //}

        //public Order GetOrderById(int orderId)
        //{
        //    using (SqlCeConnection connection = WorkDataBase.CreateConnection())
        //    {
        //        if (connection.State != ConnectionState.Open)
        //            connection.Open();

        //        string sqlQuery = "SELECT IdOrder, DateOrder, SumOrder, NumberOrder, VisitId, SkladId, State" +
        //                          " FROM Orders" +
        //                          " Where IdOrder = @IdOrder";
        //        using (SqlCeCommand com = new SqlCeCommand(sqlQuery, connection))
        //        {
        //            com.Parameters.Add("@IdOrder", orderId);
        //            return loadOrders(com).First();

        //        }
        //    }
        //}

        public Order CreateOrder(Order order)
        {
            using (SqlCeConnection connection = new SqlCeConnection(_connectionString))
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                string sqlQuery =
                    "insert into Orders (VisitId, DateOrder, NumberOrder, SumOrder, SkladId, State) " +
                    "values (@VisitId, @DateOrder, @NumberOrder, @SumOrder, @SkladId, @State)";
                using (SqlCeCommand com = new SqlCeCommand(sqlQuery, connection))
                {
                    com.Parameters.Add("@VisitId", order.VisitId);
                    com.Parameters.Add("@DateOrder", order.DateOrder);
                    com.Parameters.Add("@NumberOrder", order.NumberOrder);
                    com.Parameters.Add("@SumOrder", SqlDbType.Float).Value = 0.00f;
                    com.Parameters.Add("@SkladId", order.SkladId);
                    com.Parameters.Add("@State", order.NotDelivery ? "V" : "R");
                    if (com.ExecuteNonQuery() < 1)
                    {
                        throw new Exception("Не добавлено жодного запису");
                    }
                }
            }
            //List<Order> orders = GetOrdersByVisit(order.VisitId, false);
            //return orders.Single(o => o.IdOrder == (orders.Max(or => or.IdOrder)));
            return null;
        }

        public void DeleteOrder(int idOrder)
        {
            using (SqlCeConnection connection = new SqlCeConnection(_connectionString))
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                string sqlQuery = "delete from Orders where IdOrder = @IdOrder";
                using (SqlCeCommand com = new SqlCeCommand(sqlQuery, connection))
                {
                    com.Parameters.Add("@IdOrder", idOrder);
                    if (com.ExecuteNonQuery() < 1)
                    {
                        throw new Exception("Не видалено жодного запису");
                    }
                }
            }
        }

        public void UpdateOrder(Order order)
        {
            using (SqlCeConnection connection = new SqlCeConnection(_connectionString))
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                string sqlQuery =
                    "update orders set " +
                        "NumberOrder = @NumberOrder, DateOrder = @DateOrder, SumOrder = @SumOrder, SkladId = @SkladId, State = @State " +
                        "where idOrder = @IdOrder";
                using (SqlCeCommand com = new SqlCeCommand(sqlQuery, connection))
                {
                    com.Parameters.Add("@IdOrder", order.IdOrder);
                    com.Parameters.Add("@NumberOrder", order.NumberOrder);
                    com.Parameters.Add("@DateOrder", order.DateOrder);
                    com.Parameters.Add("@SumOrder", order.SumOrder);
                    com.Parameters.Add("@SkladId", order.SkladId);
                    char state;
                    if (order.NotDelivery)
                        state = 'V';
                    else if (order.IsReadOnly)
                        state = 'E';
                    else
                        state = 'R';
                    com.Parameters.Add("@State", state.ToString());
                    if (com.ExecuteNonQuery() < 1)
                    {
                        throw new Exception("Не змінено жодного запису");
                    }
                }
            }
        }

    }
    
}
